package com.example.book_subscribe.bookList.mapper;

import com.example.book_subscribe.bookList.dto.*;
import com.example.book_subscribe.bookList.result.*;
import com.example.book_subscribe.login.dto.UserDto;
import org.apache.ibatis.annotations.*;

import java.util.ArrayList;
import java.util.List;

@Mapper
public interface BookListMapper {
//    @Select("select book_name,book_author,book_url,book_introduce from book_list as bl where book_name LIKE CONCAT('%', #{inputText}, '%') OR book_author LIKE CONCAT('%', #{inputText}, '%')")
ArrayList<BookResult> searchBookList(@Param("inputAll") BookListDto inputAll,
                                     @Param("offset") long offset);
    // 在 BookListMapper.java 中添加
    ArrayList<CategoryTopBooksResult> selectTopBooksByCategory();
    @Select("select book_id,book_name,book_author,book_url,book_introduce from book_list  where book_id=#{bookId}")
    ArrayList<BookResult> searchBookDetail(@Param("bookId") String bookId);
    @Select("SELECT book_id,book_name, book_url FROM book_list where book_id!=#{bookId} ORDER BY RAND() LIMIT 4")
    ArrayList<BookResult> getRandomBook(@Param("bookId") String bookId);
    @Select("select count(*) from comment_list where open_id=#{openid} and book_id=#{bookId}")
    boolean searchOpenid(@Param("openid") String openid,@Param("bookId") String bookId);
    @Insert("insert into comment_list (open_id, book_id, user_comments, comments_content, `like`) values (#{bookComment.openId}, #{bookComment.bookId}, #{bookComment.userComments}, #{bookComment.commentsContent}, 0)")
    void insertComment(@Param("bookComment") BookCommentDto bookComment);
    @Update("update comment_list set user_comments = #{bookComment.userComments}, comments_content = #{bookComment.commentsContent} where open_id = #{bookComment.openId} and book_id = #{bookComment.bookId}")
    void updateComment(@Param("bookComment") BookCommentDto bookComment);
    @Select("select user_comments from comment_list where open_id=#{openid} and book_id=#{bookid}")
    String searchUserComments(@Param("openid") String openid,@Param("bookid") String bookid);
    @Select("select comments_content from comment_list where open_id=#{openid} and book_id=#{bookId}")
    String searchUserLastComments(@Param("openid") String openid,@Param("bookId") String bookId);
    @Select("select count(*) from comment_list where book_id=#{bookId}")
    long searchAllComments(@Param("bookId") String bookId);
    @Select("select count(*) from comment_list where user_comments='好看' and book_id=#{bookId}")
    long searchGoodComments(@Param("bookId") String bookId);
    @Select("select count(*) from comment_list where user_comments='一般' and book_id=#{bookId}")
    long searchOrdinaryComments(@Param("bookId") String bookId);
    @Select("select count(*) from comment_list where user_comments='不行' and book_id=#{bookId}")
    long searchBadComments(@Param("bookId") String bookId);
    @Select("SELECT c.user_comments, c.comments_content, u.user_url, u.user_wxname, c.like, cl.like_state,c.open_id " +
            "FROM comment_list c " +
            "JOIN user u ON c.open_id = u.open_id " +
            "LEFT JOIN comment_like cl ON cl.openid_comment = c.open_id AND cl.book_id=c.book_id AND cl.openid_user = #{openiduser} " +
            "WHERE c.book_id = #{bookId} " +
            "ORDER BY c.like DESC")
    ArrayList<BookCommentsResult> getAllComments(@Param("bookId") String bookId, @Param("openiduser") String openiduser);

    @Update("UPDATE comment_list cl " +
            "SET cl.like = cl.like + 1 " +
            "WHERE cl.book_id = #{bookId} "+
            "AND cl.open_id = #{openidComment} "
            )
    void addCommentList(@Param("bookId") String bookId,@Param("openidComment") String openidComment);
    @Update("UPDATE comment_list cl " +
            "SET cl.like = cl.like - 1 " +
            "WHERE cl.book_id = #{bookId} "+
            "AND cl.open_id = #{openidComment} "
            )
    void subtractCommentList(@Param("bookId") String bookId,@Param("openidComment") String openidComment);

    @Select("select count(*) from comment_like where openid_user=#{bookCommentState.openidUser} and book_id=#{bookCommentState.bookId} and openid_comment=#{bookCommentState.openidComment}")
    boolean searchCommentLike(@Param("bookCommentState") BookCommentStateDto bookCommentState);
    @Insert("insert into comment_like (openid_user,book_id,openid_comment,like_state) values (#{bookCommentState.openidUser},#{bookCommentState.bookId},#{bookCommentState.openidComment},#{bookCommentState.likeState})")
    void insertCommentLike(@Param("bookCommentState") BookCommentStateDto bookCommentState);
    @Update("UPDATE comment_like SET like_state = #{bookCommentState.likeState} WHERE openid_user = #{bookCommentState.openidUser} AND book_id = #{bookCommentState.bookId} and openid_comment=#{bookCommentState.openidComment}")
    void updateCommentLike(@Param("bookCommentState") BookCommentStateDto bookCommentState);

    @Select("select book_file from book_list where book_id=#{bookId}")
    String searchBookFileUrl(@Param("bookId") String bookId);
    @Select("select count(*) from book_progress where open_id=#{bookProgress.openId} and book_id=#{bookProgress.bookId}")
    boolean searchProgress(@Param("bookProgress") BookProgressDto bookProgress);
    @Insert("insert into book_progress (open_id,book_id,now_page,page_progress) values (#{bookProgress.openId},#{bookProgress.bookId},#{bookProgress.nowPage},#{bookProgress.pageProgress})")
    void insertProgress(@Param("bookProgress") BookProgressDto bookProgress);
    @Update("UPDATE book_progress SET now_page = #{bookProgress.nowPage}, page_progress = #{bookProgress.pageProgress} WHERE open_id = #{bookProgress.openId} AND book_id = #{bookProgress.bookId}")
    void updateProgress(@Param("bookProgress") BookProgressDto bookProgress);
    @Select("select now_page,page_progress from book_progress where open_id=#{bookProgress.openId} and book_id=#{bookProgress.bookId}")
    BookProgressResult getProgress(@Param("bookProgress") BookProgressDto bookProgress);
    @Select("select count(*) from book_note where open_id=#{bookNote.openId} and book_id=#{bookNote.bookId}")
    boolean searchNote(@Param("bookNote") BookNoteDto bookNote);
    @Insert("insert into book_note (open_id,book_id,note_text) values (#{bookNote.openId},#{bookNote.bookId},#{bookNote.noteText})")
    void insertNote(@Param("bookNote") BookNoteDto bookNote);
    @Update("UPDATE book_note SET note_text = #{bookNote.noteText} WHERE open_id = #{bookNote.openId} AND book_id = #{bookNote.bookId}")
    void updateNote(@Param("bookNote") BookNoteDto bookNote);
    @Select("select note_text from book_note where open_id=#{bookNote.openId} and book_id=#{bookNote.bookId}")
    String getNote(@Param("bookNote") BookNoteDto bookNote);
    @Select("select count(*) from subscribe where open_id=#{bookSubscribe.openId} and book_id=#{bookSubscribe.bookId}")
    boolean searchSubscribe(@Param("bookSubscribe") BookSubscribeDto bookSubscribe);
    @Insert("insert into subscribe (open_id,book_id,state) values (#{bookSubscribe.openId},#{bookSubscribe.bookId},#{bookSubscribe.state})")
    void insertSubscribe(@Param("bookSubscribe") BookSubscribeDto bookSubscribe);
    @Update("UPDATE subscribe SET state = #{bookSubscribe.state} WHERE open_id = #{bookSubscribe.openId} AND book_id = #{bookSubscribe.bookId}")
    void updateSubscribe(@Param("bookSubscribe") BookSubscribeDto bookSubscribe);
    @Select("select state from subscribe where open_id=#{bookSubscribe.openId} and book_id=#{bookSubscribe.bookId}")
    Boolean getSubscribe(@Param("bookSubscribe") BookSubscribeDto bookSubscribe);

    @Select("select category_name,category_type from category where category_parent=#{classify}")
    ArrayList<BookClassifyResult> getAllClassify(@Param("classify") String classify);
    @Select("select book_id,book_name,book_author,book_url,book_introduce from book_list where book_type=#{category_type}")
    ArrayList<BookResult> getClassifyBooks(@Param("category_type") int category_type);
    @Select("SELECT b.book_id,b.book_name, b.book_author, b.book_url, b.book_introduce " +
            "FROM book_list b " +
            "JOIN category_parent c ON ROUND(b.book_type / c.category_parent_type, 1) = 1 " +
            "WHERE c.category_parent_name = #{classify}")
    ArrayList<BookResult> getClassifyAllBooks(@Param("classify") String classify);

    @Select("select count(*) from search_history where open_id=#{bookSubscribe.openId} and search_text=#{bookSubscribe.bookName}")
    boolean searchHistory(@Param("bookSubscribe") BookSubscribeDto bookSubscribe);
    @Insert("insert into search_history (open_id,search_text,search_number) values (#{bookSubscribe.openId},#{bookSubscribe.bookName},1)")
    void insertHistory(@Param("bookSubscribe") BookSubscribeDto bookSubscribe);
    @Update("UPDATE search_history SET search_number = search_number+1 WHERE open_id = #{bookSubscribe.openId} AND search_text = #{bookSubscribe.bookName}")
    void updateHistory(@Param("bookSubscribe") BookSubscribeDto bookSubscribe);

    ArrayList<BookResult> recommendBooks(@Param("openid") String openid,
                                         @Param("inputAll") BookListDto inputAll,
                                         @Param("offset") long offset);

    @Select("SELECT b.book_id,b.book_name, b.book_author, b.book_url, b.book_introduce " +
            "FROM book_list b " +
            "LEFT JOIN book_progress bp ON b.book_name = bp.book_name " +
            "GROUP BY b.book_id,b.book_name, b.book_author, b.book_url, b.book_introduce " +
            "ORDER BY COUNT(bp.book_id) DESC "+
            "limit #{offset}, #{inputAll.pageSize}")
    ArrayList<BookResult> getRankBooks(@Param("inputAll") BookListDto inputAll,
                                       @Param("offset") long offset);

    @Select("select bl.book_id,bl.book_name,bl.book_url from book_list bl " +
            "left join book_progress bp on bl.book_id=bp.book_id " +
            "where bp.open_id=#{openid}")
    ArrayList<BookResult> searchReadHistory(@Param("openid") String openid);
}

